package com.yuki.fwwb.DAO;

import com.yuki.fwwb.Entity.Trips;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;
@Repository
@Mapper
public interface TripsDAO {

    @Select("SELECT MONTH(arrival_time) as month,count(*) as num from trips GROUP BY MONTH(arrival_time) order by month")
    List<Map> findMonthSum();
    @Select("SELECT channel_number,count(*) as num from trips GROUP BY  channel_number")
    List<Map> findChannelNumberSum();
    @Select("SELECT   date_format(arrival_time,'%w') as  week,count(*) as num FROM trips  WHERE MONTH(arrival_time)=#{month} GROUP BY week order by week")
    List<Map> findByWeekOfMonth(int month);

    /**
     * 当周按星期类流量
     * @param week
     * @return
     */
    @Select("SELECT date_format(arrival_time,'%w') week,count(*) as num FROM trips  WHERE  date_format(arrival_time,'%u')=#{week} GROUP BY week order by week")
    List<Map> findByWeek(int week);

    /**
     * 当周总流量
     * @param week
     * @return
     */
    @Select("SELECT date_format(arrival_time,'%u') week,count(*) as num FROM trips GROUP BY week HAVING week=#{week}")
    List<Map> findByWeekAll(int week);

    @Select("SELECT  DAY(arrival_time) as day,count(*) as num from trips WHERE MONTH(arrival_time)=#{month} GROUP BY  DAY(arrival_time) ORDER BY " +
            "DAY(arrival_time)")
    List<Map> findByMonthDay(int month);



    @Select("SELECT  date_format(arrival_time,'%c-%e') as day,count(*) as num,entry_name from trips WHERE MONTH(arrival_time)=#{month} GROUP BY " +
            " DAY(arrival_time),entry_name HAVING day=#{day}")
    List<Map> findByOneStationInOfDay(@Param("month") int month,@Param("day") String day);
    @Select("SELECT  date_format(outbound_time,'%c-%e') as day,count(*) as num,outbound_name from trips WHERE MONTH(outbound_time)=#{month} GROUP BY " +
            " DAY(outbound_time),outbound_name HAVING day=#{DAY}")
    List<Map> findByOneStationOutOfDay(@Param("month") int month,@Param("day") String day);
    @Select("SELECT MONTH(arrival_time) as month,entry_name,count(*) as num from trips GROUP BY" +
            " MONTH(arrival_time),entry_name HAVING  month=#{month}")
    List<Map> findByOneStationInOfMonth(@Param("month") int month);
    @Select("SELECT MONTH(outbound_time) as month,outbound_name,count(*) as num from trips GROUP BY " +
            "MONTH(outbound_time),outbound_name HAVING month=#{month}")
    List<Map> findByOneStationOutOfMonth(@Param("month") int month);

    /**
     * 单日全部站台出入量
     * @param month
     * @param day
     * @return
     */
    @Select("SELECT  data1.day,data1.entry_name as station,data1.num as in_num,data2.num as out_num from " +
            " (SELECT  date_format(arrival_time,'%c-%e') as day,count(*) as num,entry_name from trips WHERE MONTH(arrival_time)=#{month} GROUP BY  " +
            "day,entry_name HAVING day=#{day}) as data1 ,(SELECT  date_format(outbound_time,'%c-%e') as day,count(*) as num,outbound_name from " +
            "trips WHERE MONTH(outbound_time)=#{month} GROUP BY  day,outbound_name HAVING day=#{day}" +
            ") as data2 where data1.day=data2.day and data1.entry_name=data2.outbound_name")
    List<Map> findByOneDayAllStationInOutOfMonth(@Param("month") int month,@Param("day") String day);

    /**
     * 当月全部站台出入量
     * @param month
     * @param
     * @return
     */
    @Select("SELECT data1.month,data1.entry_name,data1.num as in_num,data2.num as out_num from (SELECT MONTH(arrival_time) as month,entry_name,count(*) as num from trips GROUP BY MONTH(arrival_time),entry_name HAVING" +
            "  month=#{month}) as data1 ,(" +
            "SELECT MONTH(outbound_time) as month,outbound_name,count(*) as num from trips GROUP BY MONTH(outbound_time),outbound_name HAVING  month=#{month}" +
            ") as data2 where data1.entry_name=data2.outbound_name")
    List<Map> findByOneMonthAllStationInOut(@Param("month") int month);

    /**
     * 单日单个站台出入量
     * @param month
     * @param day
     * @return
     */
    @Select("SELECT  data1.day,data1.entry_name as station,data1.num as in_num,data2.num as out_num from " +
            " (SELECT  date_format(arrival_time,'%c-%e') as day,count(*) as num,entry_name from trips WHERE MONTH(arrival_time)=#{month} GROUP BY  " +
            "day,entry_name HAVING day=#{day}) as data1 ,(SELECT  date_format(outbound_time,'%c-%e') as day,count(*) as num,outbound_name from " +
            "trips WHERE MONTH(outbound_time)=#{month} GROUP BY  day,outbound_name HAVING day=#{day}" +
            ") as data2 where data1.day=data2.day and data1.entry_name=data2.outbound_name and data1.entry_name=(SELECT sta_name from station WHERE sta_id=#{station})")
    List<Map> findByOneDayStationInOutOfMonth(@Param("month") int month,@Param("day") String day,@Param("station") String station);
    /**
     * 当月站台出入量
     * @param month
     * @param
     * @return
     */
    @Select("SELECT data1.month,data1.entry_name,data1.num as in_num,data2.num as out_num from (SELECT MONTH(arrival_time) as month,entry_name,count(*) " +
            "as num from trips GROUP BY MONTH(arrival_time),entry_name HAVING month=#{month}) as data1 ,(" +
            "SELECT MONTH(outbound_time) as month,outbound_name,count(*) as num from trips GROUP BY MONTH(outbound_time),outbound_name HAVING  month=#{month}" +
            ") as data2 where data1.entry_name=data2.outbound_name and data1.entry_name=(SELECT sta_name from station WHERE sta_id=#{station})")
    List<Map> findByOneMonthStationInOut(@Param("month") int month,@Param("station") String station);




}
